package xju.rjxy.shop.mapper.impl;

import xju.rjxy.shop.mapper.GameMapper;
import xju.rjxy.shop.model.domain.Game;
import xju.rjxy.shop.model.vo.GameVO;
import xju.rjxy.shop.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 游戏数据访问接口实现类
 */
public class GameMapperImpl implements GameMapper {
    private final QueryRunner runner = new QueryRunner();

    /**
     * 查询游戏列表
     */
    @Override
    public List<GameVO> selectGameList() {
        String sql = "SELECT " +
                "id, " +
                "game_name AS gameName, " +
                "category, " +
                "price, " +
                "description, " +
                "pic, " +
                "score, " +
                "create_time AS createTime, " +
                "flag, " +
                "status_code AS statusCode, " +
                "deleted " +
                "FROM games " +
                "WHERE flag = 2 AND deleted = 0";

        List<GameVO> gameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            gameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(GameVO.class)
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return gameList;
    }
    
    /**
     * 查询热卖游戏
     */
    @Override
    public List<GameVO> selectHotGames(int limit) {
        String sql = "SELECT " +
                "g.id, " +
                "g.game_name AS gameName, " +
                "g.category, " +
                "g.price, " +
                "g.description, " +
                "g.pic, " +
                "g.score, " +
                "g.create_time AS createTime, " +
                "g.flag, " +
                "g.status_code AS statusCode, " +
                "g.deleted, " +
                "COUNT(ug.id) AS salesCount " +
                "FROM games g " +
                "LEFT JOIN user_games ug ON g.id = ug.game_id " +
                "WHERE g.flag = 2 AND g.deleted = 0 " +
                "GROUP BY g.id " +
                "ORDER BY salesCount DESC " +
                "LIMIT ?";

        List<GameVO> gameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            gameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(GameVO.class),
                    limit
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return gameList;
    }
    
    /**
     * 查询推荐游戏
     */
    @Override
    public List<GameVO> selectRecommendedGames(Integer userId, int limit) {
        String sql = "SELECT DISTINCT " +
                "g.id, " +
                "g.game_name AS gameName, " +
                "g.category, " +
                "g.price, " +
                "g.description, " +
                "g.pic, " +
                "g.score, " +
                "g.create_time AS createTime, " +
                "g.flag, " +
                "g.status_code AS statusCode, " +
                "g.deleted " +
                "FROM games g " +
                "WHERE g.flag = 2 AND g.deleted = 0 " +
                "AND g.category IN (" +
                "    SELECT DISTINCT g2.category " +
                "    FROM user_games ug " +
                "    JOIN games g2 ON ug.game_id = g2.id " +
                "    WHERE ug.user_id = ? AND ug.flag = 2" +
                ") " +
                "AND g.id NOT IN (" +
                "    SELECT game_id FROM user_games WHERE user_id = ?" +
                ") " +
                "ORDER BY g.create_time DESC " +
                "LIMIT ?";

        List<GameVO> gameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            gameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(GameVO.class),
                    userId, userId, limit
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return gameList;
    }
    
    /**
     * 查询打折游戏
     */
    @Override
    public List<GameVO> selectDiscountGames(int limit) {
        String sql = "SELECT " +
                "id, " +
                "game_name AS gameName, " +
                "category, " +
                "price, " +
                "description, " +
                "pic, " +
                "score, " +
                "create_time AS createTime, " +
                "flag, " +
                "status_code AS statusCode, " +
                "deleted " +
                "FROM games " +
                "WHERE flag = 2 AND deleted = 0 " +
                "ORDER BY price ASC " +
                "LIMIT ?";

        List<GameVO> gameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            gameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(GameVO.class),
                    limit
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return gameList;
    }
    
    /**
     * 根据ID查询游戏详情
     */
    @Override
    public Game selectById(Integer id) {
        String sql = "SELECT id, game_name AS gameName, category, price, description, pic, score, create_time AS createTime, flag, status_code AS statusCode, deleted FROM games WHERE id = ? AND flag = 2 AND deleted = 0";
        Game game = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            game = runner.query(connection, sql, new BeanHandler<>(Game.class), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return game;
    }
    
    /**
     * 根据类别查询游戏
     */
    @Override
    public List<GameVO> selectByCategory(String category, int offset, int limit) {
        String sql = "SELECT " +
                "id, " +
                "game_name AS gameName, " +
                "category, " +
                "price, " +
                "description, " +
                "pic, " +
                "score, " +
                "create_time AS createTime, " +
                "flag, " +
                "status_code AS statusCode, " +
                "deleted " +
                "FROM games " +
                "WHERE flag = 2 AND deleted = 0 AND category = ? " +
                "ORDER BY create_time DESC " +
                "LIMIT ? OFFSET ?";

        List<GameVO> gameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            gameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(GameVO.class),
                    category, limit, offset
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return gameList;
    }
    
    /**
     * 根据价格范围查询游戏
     */
    @Override
    public List<GameVO> selectByPriceRange(Double minPrice, Double maxPrice, int offset, int limit) {
        String sql = "SELECT " +
                "id, " +
                "game_name AS gameName, " +
                "category, " +
                "price, " +
                "description, " +
                "pic, " +
                "score, " +
                "create_time AS createTime, " +
                "flag, " +
                "status_code AS statusCode, " +
                "deleted " +
                "FROM games " +
                "WHERE flag = 2 AND deleted = 0 " +
                "AND price >= ? AND price <= ? " +
                "ORDER BY price ASC " +
                "LIMIT ? OFFSET ?";

        List<GameVO> gameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            gameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(GameVO.class),
                    minPrice, maxPrice, limit, offset
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return gameList;
    }
    
    /**
     * 根据销量排序查询游戏
     */
    @Override
    public List<GameVO> selectBySalesOrder(int offset, int limit) {
        String sql = "SELECT " +
                "id, " +
                "game_name AS gameName, " +
                "category, " +
                "price, " +
                "description, " +
                "pic, " +
                "score, " +
                "create_time AS createTime, " +
                "flag, " +
                "status_code AS statusCode, " +
                "deleted " +
                "FROM games " +
                "WHERE flag = 2 AND deleted = 0 " +
                "ORDER BY create_time DESC " +
                "LIMIT ? OFFSET ?";

        List<GameVO> gameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            gameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(GameVO.class),
                    limit, offset
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return gameList;
    }
    
    /**
     * 按类别分组查询游戏
     */
    @Override
    public List<GameVO> selectGroupedByCategory() {
        String sql = "SELECT " +
                "id, " +
                "game_name AS gameName, " +
                "category, " +
                "price, " +
                "description, " +
                "pic, " +
                "score, " +
                "create_time AS createTime, " +
                "flag, " +
                "status_code AS statusCode, " +
                "deleted " +
                "FROM games " +
                "WHERE flag = 2 AND deleted = 0 " +
                "ORDER BY category ASC, create_time DESC";

        List<GameVO> gameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            gameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(GameVO.class)
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return gameList;
    }
} 